package demo0109_jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Created by ztt on 2017/9/13.
 */
@Repository
public class JdbcUtil {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 查
    public Article find(int id) {
        final String sql = "select * from article where id=?";
        Article ac = jdbcTemplate.queryForObject(sql, new Object[] { id },
                new BeanPropertyRowMapper<Article>(Article.class));
        return ac;
    }

    // 改
    public boolean update(Article a) {
        final String sql = "update article set title=?,content=? where id=?";
        int rc = jdbcTemplate.update(sql, a.getTitle(), a.getContent());
        return 1 == rc;
    }

    // 删
    public boolean delete(int id) {
        final String sql = "delete from article where id=?";
        int rc = jdbcTemplate.update(sql, id);
        return 1 == rc;
    }

    // 增
    public int add(final Article a) {
        KeyHolder holder = new GeneratedKeyHolder();
        final String sql = "insert into article(title,content) values(?,?)";
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, a.getTitle());
                ps.setString(2, a.getContent());
                return ps;
            }
        }, holder);
        return holder.getKey().intValue();
    }

    //根据开始和限定条数查询多条
    public List<Article> list(int begin, int limit) {
        final String sql = "select * from article limit ?,?";
        return jdbcTemplate.query(sql, new Object[] { begin, limit },
                new BeanPropertyRowMapper<Article>(Article.class));
    }


    /**
     * 拼接语句，往表里面插入数据
     */
    public static int saveObj(JdbcTemplate jt,String tableName,Object obj){
        int re = 0;
        try{
            String sql = " insert into " + tableName + " (";
            Map<String,String> map = ObjectUtil.getProperty(obj);
            Set<String> set = map.keySet();
            for(String key : set){
                sql += (key + ",");
            }
            sql += " tableName ) ";
            sql += " values ( ";
            for(String key : set){
                sql += ("'" + map.get(key) + "',");
            }
            sql += ("'" + tableName + "' ) ");
            re = jt.update(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return re;
    }

    /**
     * 根据表名称创建一张表
     * @param tableName
     */
    public  int createTable(String tableName,Object obj){
        StringBuffer sb = new StringBuffer("");
        sb.append("CREATE TABLE `" + tableName + "` (");
        sb.append(" `id` int(11) NOT NULL AUTO_INCREMENT,");
        Map<String,String> map = ObjectUtil.getProperty(obj);
        Set<String> set = map.keySet();
        for(String key : set){
            sb.append("`" + key + "` varchar(255) DEFAULT '',");
        }
        sb.append(" PRIMARY KEY (`id`)");
        sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
        try {
            jdbcTemplate.update(sb.toString());
            return 1;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询数据库是否有某表
     * @param jt
     * @param tableName
     * @return
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public  boolean getAllTableName(JdbcTemplate jt,String tableName) throws Exception {
        Connection conn = jt.getDataSource().getConnection();
        ResultSet tabs = null;
        try {
            DatabaseMetaData dbMetaData = conn.getMetaData();
            String[]   types   =   { "TABLE" };
            tabs = dbMetaData.getTables(null, null, tableName, types);
            if (tabs.next()) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            tabs.close();
            conn.close();
        }
        return false;
    }
}
